﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;

namespace FastData.DataProvider
{
    public class OracleProviderFactory : DataProviderFactory
    {
        public override string ProviderName
        {
            get { return "Oracle.Data.Client"; }
        }

        internal override string CreateFunctionText(Function query, ExecuteType type)
        {
            return "SELECT " + base.CreateFunctionText(query, type) + " FROM DUAL";
        }

        internal override string CreateProcedureText(Procedure query, ExecuteType type)
        {
            if (type == ExecuteType.Reader)
            {
                query.Parameters.Add(CreateRefCursorParameter("FastDataRefCursor"));
            }
            return base.CreateProcedureText(query, type);
        }

        internal override string CreateSelectText(QueryBase query, ExecuteType type)
        {
            string from = GetDbObjectName(query.TableName);
            foreach (var join in query.Joins)
            {
                from += " " + join.Type.GetName() + " JOIN " + GetDbObjectName(join.TableName) + " ON " + join.Where;
            }

            string selectText;
            if (query.SkipCount == 0)
            {
                if (query.TakeCount == 0)
                {
                    selectText = "SELECT " + query.FieldNames + " FROM " + from + " " + query.WhereText + " " + query.OrderByText;
                }
                else
                {
                    string select1 = "SELECT " + query.FieldNames + " FROM " + from + " " + query.WhereText + " " + query.OrderByText;
                    selectText = "SELECT " + query.FieldNames + " FROM (" + select1 + ") WHERE ROWNUM<=" + query.TakeCount;
                }
            }
            else
            {
                //selectText= "SELECT * FROM (" +
                //        "SELECT * FROM (SELECT " + query.FieldNames + ", row_number() OVER (" + query.OrderByText + ") AS row_number " +
                //        "FROM ( SELECT " + query.FieldNames + " FROM " + GetSafeObjectName(query.TableName) + ")) " +
                //        "WHERE (row_number >" + query.SkipCount + ") " + query.OrderByText + ") WHERE (ROWNUM <=" + query.TakeCount + ")";

                string select1 = "SELECT " + query.FieldNames + " FROM " + from + " " + query.WhereText + " " + query.OrderByText;
                string select2 = "SELECT ROWNUM ROWNUM1," + query.FieldNames + " FROM (" + select1 + ")" + (query.TakeCount > 0 ? " WHERE ROWNUM<=" + (query.SkipCount + query.TakeCount) + "" : "");
                selectText = "SELECT " + query.FieldNames + " FROM (" + select2 + ") WHERE ROWNUM1>" + query.SkipCount;
            }

            return selectText;
        }

        public override string GetDbObjectName(string name)
        {
            name = name.Trim();
            if (name == "*" || name.IndexOf('(') > 0 || name.IndexOf(' ') > 0)
            {
                return name;
            }

            return string.Join(".", name.Split('.').Select(n => n[0] == '"' ? n : "\"" + n + "\""));
        }

        public override string GetDbParameterName(string name)
        {
            return ":" + name;
        }

        protected override object GetSafeValue(object value, bool antiXSS)
        {
            if (value is bool)
            {
                return (bool)value ? 1 : 0;
            }
            return base.GetSafeValue(value, antiXSS);
        }

        public DbParameter CreateRefCursorParameter(string name)
        {
            Assembly oracleAssembly = ProviderFactory.GetType().Assembly;
            Type parameterType = oracleAssembly.GetType("Oracle.Data.Client.OracleParameter");
            Type oracleDbTypeEnum = oracleAssembly.GetType("Oracle.Data.Client.OracleDbType");

            DbParameter param = (DbParameter)Activator.CreateInstance(parameterType);
            parameterType.GetProperty("OracleDbType").SetValue(param, Enum.Parse(oracleDbTypeEnum, "RefCursor"));
            parameterType.GetProperty("ParameterName").SetValue(param, name);
            parameterType.GetProperty("Direction").SetValue(param, ParameterDirection.Output);

            return param;
        }

        internal override string CreateInsertText(QueryBase query, ExecuteType type)
        {
            if (type == ExecuteType.NoneQuery)
            {
                return base.CreateInsertText(query, type);
            }
            else
            {
                var insertText = base.CreateInsertText(query, type);
                var dbIdName = GetDbObjectName(query.IdName);

                query.Parameters.Add(CreateRefCursorParameter(query.IdName));

                return "declare " + dbIdName + " number(38,0);\n" +
                        "begin\n" +
                        insertText + "\n" +
                        "returning " + dbIdName + " into " + dbIdName + ";\n" +
                        "open " + GetDbParameterName(query.IdName) + " for select " + dbIdName + " as " + dbIdName + " from dual;\n" +
                        "end;";
            }
        }

        internal override string CreateBatchText(QueryBase query, ExecuteType type, Query batchQuery)
        {
            if (query is SelectQuery)
            {
                string refCursorParameterName = "FastDataRefCursor" + batchQuery.Parameters.Count;
                batchQuery.Param(CreateRefCursorParameter(refCursorParameterName));

                return "open " + GetDbParameterName(refCursorParameterName) + " for " + base.CreateBatchText(query, type, batchQuery);
            }
            return base.CreateBatchText(query, type, batchQuery);
        }

        internal override Query CreateBatchQuery(List<QueryBase> queries, ExecuteType type)
        {
            Query query = base.CreateBatchQuery(queries, type);
            query.CommandText = "begin " + query.CommandText + " end;";

            return query;
        }

        public override SelectQuery GetViewTableUsage(DataAccess data, string viewName)
        {
            throw new NotImplementedException();
        }

        public override bool SupportBatchCommand
        {
            get { return true; }
        }

        public override bool SupportInsertedId
        {
            get { return true; }
        }

        internal override string LastIdentityText
        {
            get { throw new NotSupportedException(); }
        }
    }
}
